<?php
/**
 +------------------------------------------------------------------------------
 * AssetsprintAction控制器类
 +------------------------------------------------------------------------------
 * @category   资产报表
 * @package  app
 * @subpackage  Action
 * @author   朝闻道 <tb>
 * @date 2014.04.03
 * @time  下午2:44:22
 +------------------------------------------------------------------------------
 */
class AssetsprintAction extends BaseAction {
	protected $dao,$newpro;
	
	function _initialize() {
		parent::_initialize ();
		
		import ( 'Com.Newpro' );
		$this->newpro = new Newpro ();
		
	}
	
	/**
	 * 消费记录
	 * @author tb
	 * @date 2014.04.03
	 * */
	function xfprint(){
		$date = empty($_REQUEST['txttime']) ? date("Y-m",mktime(0, 0 , 0,date("m")-1,1,date("Y"))) : $_REQUEST['txttime'];
		$btime = gettime($date."-01 00:00:00");
		$etime = gettime($date."-31 23:59:59");
		
		$money = empty($_REQUEST['gtmoney']) ? 10000000 : $_REQUEST['gtmoney']*100;
		
		$dao = M("`order` o");
		$where['o.status'] = array('gt',0);
		$where['o.pay_time'] = array('between', $btime.','.$etime);
		$count = $dao->where($where)->group('userid')->having("sum(amount) >= $money")->select();
		$count = count($count);
		$page=new Page($count,$this->listrows);
		$offset = $page -> firstRow;
		$length = $page -> listRows;		
		
// 		$sql = "select o.userid 用户编号,FROM_UNIXTIME(MIN(o.pay_time),'%Y%m%d') 消费时间,u.username 帐号,ub.realname 名称,FORMAT(SUM(o.amount)/100,2) 消费金额,
// u.email 邮件,ub.company 店名,ub.tel 电话,u.mobile 手机,CASE ub.sex WHEN 1 THEN '男' ELSE '女' END 性别,ub.address 地址 
// from `order` o LEFT JOIN `user` u on u.id=o.userid LEFT JOIN user_b2b ub on o.userid = ub.user_id 
// WHERE FROM_UNIXTIME(o.pay_time,'%Y%m') = 201403 AND o.`status` > 0
// GROUP BY o.userid HAVING SUM(o.amount) >= 10000000 ORDER BY o.userid DESC";
		
		$res = $dao->field("o.userid userid,FROM_UNIXTIME(MIN(o.pay_time),'%Y%m%d') pay_time,u.username username,ub.realname realname,FORMAT(SUM(o.amount)/100,2) amount,
u.email email,ub.company,ub.tel ,u.mobile ,CASE ub.sex WHEN 1 THEN '男' ELSE '女' END sex,ub.address ")
		->join("left join `user` as u on u.id = o.userid")
		->join(" user_b2b as ub on o.userid = ub.user_id")
		->where($where)->group('o.userid')->having("sum(o.amount)>$money")->limit($offset,$length)->select();

		//echo $dao->getLastSql();
		$this->assign("totalCount",$count);
		$this->assign('list',$res);
		$this->assign('date',$date);
		$this->assign('money',$money/100);
		$this->display();
	}
	
	
	/**
	 * 消费记录到处excel
	 */
	function out(){
		//$uids = array();
		//$uids = $_REQUEST['ids'];dump($uids);
		
		//dump($uids);exit;
		///$uids = str_replace("'","\" ",$uids);
			//	dump($uids);
			//	exit;
			
		//$uids = "11458,11599,11645";
			
		$date = empty($_REQUEST['txttime']) ? date("Y-m",mktime(0, 0 , 0,date("m")-1,1,date("Y"))) : $_REQUEST['txttime'];
		$date = str_replace("_","-",$date);
		$btime = gettime($date."-01 00:00:00");
		$etime = gettime($date."-31 23:59:59");
		
		$money = empty($_REQUEST['gtmoney']) ? 10000000 : $_REQUEST['gtmoney']*100;
		
		$dao = M("`order` o");
		$where['o.status'] = array('gt',0);
		$where['o.pay_time'] = array('between', $btime.','.$etime);
			
		if($uids){
			//选择导出
			$where['o.userid'] = array("in",$uids);
		}
		

		$res = $dao->field("o.userid userid,FROM_UNIXTIME(MIN(o.pay_time),'%Y%m%d') pay_time,u.username username,ub.realname realname,FORMAT(SUM(o.amount)/100,2) amount,
u.email email,ub.company,ub.tel ,u.mobile ,CASE ub.sex WHEN 1 THEN '男' ELSE '女' END sex,ub.address ")
		->join("left join `user` as u on u.id = o.userid")
		->join(" user_b2b as ub on o.userid = ub.user_id")
		->where($where)->group('o.userid')->having("sum(o.amount)>$money")->select();
		echo $dao->getLastSql();exit;
		
		$path = $this->newpro->xfprint_xls($res);
		// 		dump($path);
		// 		exit;
		$this->assign('path',$path);
		$this->display();
	}
	
	
}//end AssetsprintAction